package com.yeung.jdbc;

import com.yeung.beans.Employee;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class Main {
    private JdbcTemplate jdbcTemplate;
    private ClassPathXmlApplicationContext context;

    @Before
    public void init() {
        this.context = new ClassPathXmlApplicationContext("spring-config.xml");
        this.jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
    }

    @Test
    public void 数据库连接测试() throws SQLException {
        DataSource bean = context.getBean(DataSource.class);
        Connection connection = bean.getConnection();
        System.out.println(connection);
    }

    @Test
    public void 插入测试() {
        String sql = "insert into tbl_employee(lastName,gender,email) values(?,?,?)";
        int result = jdbcTemplate.update(sql, "yeung", "男", "dkdki@live.com");
        System.out.println(result);
    }

    @Test
    public void 批量插入测试() {
        String sql = "insert into tbl_employee(lastName,gender,email) values(?,?,?)";
        List<Object[]> batchArgs = new ArrayList<>();
        batchArgs.add(new Object[]{"a57发到a", "男", "dls5t66e@edu.com"});
        batchArgs.add(new Object[]{"a432a", "女", "dls344e@edu.com"});
        batchArgs.add(new Object[]{"afaa", "男", "dlsec23e@edu.com"});
        batchArgs.add(new Object[]{"afadsa", "女", "dl744se@edu.com"});
        batchArgs.add(new Object[]{"a444566a", "男", "dl885se@edu.com"});
        int[] rows = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println("受影响的行数" + Arrays.toString(rows));
    }

    @Test
    public void 更新测试() {
        String sql = "update tbl_employee set lastName=? where  id=?";
        int result = jdbcTemplate.update(sql, "kangkang", 3);
        System.out.println(result);
    }

    @Test
    public void 查询一条数据封装成对象() {
        String sql = "select id, lastName,gender,email from tbl_employee where id=?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 4);
        System.out.println(employee);
    }

    @Test
    public void 查询实体类集合() {
        String sql = "select id,lastName,gender,email from tbl_employee";
        BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        List<Employee> list = jdbcTemplate.query(sql, rowMapper);
        for (Employee employee : list) {
            System.out.println(employee);
        }
    }

    @Test
    public void 查询单个列的值或者做统计查询() {
        String sql = "select count(id) from tbl_employee";
        Long aLong = jdbcTemplate.queryForObject(sql, Long.class);
        System.out.println(aLong);
    }

    @Test
    public void 注入方式查询() {
        EmployeeDao dao = (EmployeeDao) context.getBean(EmployeeDao.class);
        if (dao == null) System.out.println("dao is null");
        Employee employee = dao.get(4);
        System.out.println(employee);
    }

    /**
     * 可以对参数取名字,方便参数多的时候赋值
     * 可维护性更高,但是过于繁琐
     * NamedParameterJdbcTemplate
     */
    public void 带参数的Template测试() {
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = context.getBean(NamedParameterJdbcTemplate.class);
        String sql = "insert into tbl_employee(lastName,email,gender) values(:ln,:email,:gender)";
        Map<String, Object> paraMap = new HashMap<>();
        paraMap.put("ln", "keros");
        paraMap.put("email", "didn@live.com");
        paraMap.put("gender", "男");
        namedParameterJdbcTemplate.update(sql, paraMap);
    }

    /**
     * 2. 优化版带参数的Template
     * 将参数命名为baen的属性名(一致)
     * 在 namedParameterJdbcTemplate.update方法中直接传入一个对象
     * 会按照属性名进行数据的注入
     */
    @Test
    public void 带参数的Template测试2() {
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = context.getBean(NamedParameterJdbcTemplate.class);
        String sql = "insert into tbl_employee(lastName,email,gender) values(:lastName,:email,:gender)";
        Employee employee = new Employee();
        employee.setEmail("33kdkds@lie.com");
        employee.setGender("女");
        employee.setLastName("jeerty");
        namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(employee));
    }
}
